{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userId</th>\n",
       "      <th>movieId</th>\n",
       "      <th>rating</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>3.0</td>\n",
       "      <td>964986456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2.0</td>\n",
       "      <td>964981761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964985228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964985919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1.0</td>\n",
       "      <td>964983920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>994</th>\n",
       "      <td>100</td>\n",
       "      <td>11</td>\n",
       "      <td>3.0</td>\n",
       "      <td>964985838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>100</td>\n",
       "      <td>6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964982235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>100</td>\n",
       "      <td>2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964985218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>100</td>\n",
       "      <td>38</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964985247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>100</td>\n",
       "      <td>23</td>\n",
       "      <td>2.0</td>\n",
       "      <td>964985558</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>999 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     userId  movieId  rating  timestamp\n",
       "0         1       27     3.0  964986456\n",
       "1         1        7     2.0  964981761\n",
       "2         1       13     5.0  964985228\n",
       "3         1        1     5.0  964985919\n",
       "4         1       28     1.0  964983920\n",
       "..      ...      ...     ...        ...\n",
       "994     100       11     3.0  964985838\n",
       "995     100        6     4.0  964982235\n",
       "996     100        2     5.0  964985218\n",
       "997     100       38     4.0  964985247\n",
       "998     100       23     2.0  964985558\n",
       "\n",
       "[999 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 1、使用pandas的read_csv函数读取CSV文件\n",
    "data = pd.read_csv('../datas/ratings.csv')\n",
    "data\n",
    "\n",
    "# 打印读取的数据\n",
    "# print(data)\n",
    "# data.shape\n",
    "# data.columns\n",
    "# data.index\n",
    "# data.dtypes\n",
    "\n",
    "# data2 = {'列1': [1, 2, 3], '列2': [4, 5, 6]}\n",
    "# df = pd.DataFrame(data2)\n",
    "# print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-10-01\n",
      "2025-03-01\n"
     ]
    }
   ],
   "source": [
    "# 获取当前日期\n",
    "today = pd.Timestamp.today()\n",
    "# 获取上个月的第一天\n",
    "first_day_of_last_month = (today - pd.offsets.MonthBegin(2)) - pd.offsets.MonthBegin(2)\n",
    "start_date = first_day_of_last_month.strftime('%Y-%m-%d')\n",
    "print(start_date)\n",
    "# 当月第一天\n",
    "end_date = today.replace(day=1).strftime('%Y-%m-%d')\n",
    "print(end_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2、使用pandas的read_csv函数读取txt文件\n",
    "# txt = pd.read_csv(\"../data/txt.txt\", sep=\"\\ \", header=None, names=['birth','age','name'])\n",
    "# txt\n",
    "# type(txt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3、使用pandas的read_excel函数读取excel文件\n",
    "excel = pd.read_excel(\"../datas/student.xlsx\")\n",
    "excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 4、使用pandas查询mysql数据库\n",
    "# 连接数据库方法一，会警告\n",
    "import pymysql\n",
    "conn = pymysql.connect(\n",
    "    host='192.168.1.9',\n",
    "    user='skullgrin_external',\n",
    "    password='0nWpNjB5',\n",
    "    database='skullgrin_external',\n",
    "    port=3360,\n",
    "    charset='utf8'\n",
    ")\n",
    "mysql_page = pd.read_sql(\"select * from com_cde\", con=conn)\n",
    "mysql_page\n",
    "# 连接数据库方法二\n",
    "# import pandas as pd\n",
    "# from sqlalchemy import create_engine\n",
    "# # 创建数据库连接引擎\n",
    "# engine = create_engine('mysql+pymysql://skullgrin_external:0nWpNjB5@192.168.1.9:3360/skullgrin_external')\n",
    "# # 使用pandas的read_sql_query函数查询数据库\n",
    "# pd.read_sql_query('SELECT * FROM com_cde', engine)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pandas数据结构\n",
    "# 1、Series:是一种类似于一维数组的对象,由一组数据(不同类型)以及一组与之相关的数据标签组成\n",
    "# 2、DataFrame\n",
    "# 3、从DataFrame中查询出Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Series 创建方法1\n",
    "s1 = pd.Series([1,'a',5.2,7])\n",
    "s1\n",
    "# s1.index 获取索引\n",
    "# s1.values 获取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Series 创建方法2\n",
    "s2 = pd.Series([1,'a',5.2,7], index=['d','b','a','c'])\n",
    "# s2\n",
    "# s2['a']\n",
    "# type(s2['a'])\n",
    "s2[['a','b']]\n",
    "type(s2[['a','b']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用python字典创建Series\n",
    "sdata = {'Ohio':3500,'Texas':72000,'Oregon':16000,'Utah':5000}\n",
    "s3 = pd.Series(sdata)\n",
    "s3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# dataframe 常用属性<br>\n",
    ".index：返回DataFrame的索引对象。<br>\n",
    ".columns：返回DataFrame的列标签。<br>\n",
    ".shape：返回DataFrame的形状，即行数和列数。<br>\n",
    ".dtypes：返回DataFrame中每列的数据类型。<br>\n",
    ".values：返回DataFrame的NumPy数组表示形式。<br>\n",
    ".T：返回DataFrame的转置。<br>\n",
    ".head() 和 .tail()：分别返回DataFrame的前五行和最后五行。可以通过参数指定行数。<br>\n",
    ".describe()：返回DataFrame的描述性统计信息，如计数、平均值、标准差等。<br>\n",
    ".loc[] 和 .iloc[]：用于基于标签选择数据。.loc[]基于行标签和列标签选择数据，而.iloc[]基于行号和列号选择数据。<br>\n",
    ".assign()：用于给DataFrame添加新列或修改现有列。可以同时为多个列赋值，无需逐一处理。例如：df = df.assign(新列1=新列1的计算方式, 新列2=新列2的计算方式)。<br>\n",
    ".drop()：用于删除DataFrame中的行或列。可以通过参数指定要删除的行或列的标签或索引。例如：df = df.drop('列名') 或 df = df.drop(index=行索引)。<br>\n",
    ".fillna()：用于填充DataFrame中的缺失值（NaN）。可以通过参数指定填充值或使用方法如.ffill()（前向填充）或.bfill()（后向填充）。例如：df = df.fillna(0) 或 df = df.ffill()。<br>\n",
    ".sort_values() 和 .sort_index()：分别用于对DataFrame的行或列进行排序。可以通过参数指定排序的列或行索引，并选择升序或降序排列。例如：df = df.sort_values(by='列名') 或 df = df.sort_index()。<br>\n",
    ".groupby()：用于对DataFrame进行分组操作，常用于数据聚合和整理。可以通过参数指定分组的列或表达式，然后对分组后的数据进行进一步处理，如求和、平均值等。例如：grouped_df = df.groupby('列名')。<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方法一：使用字段创建\n",
    "dataFram = {\n",
    "    'state':['a','b','c','d','e'],\n",
    "    'year':[2000,2001,2002,2003,2004],\n",
    "    'pop':[1.5,1.7,3.6,2.4,2.9]\n",
    "}\n",
    "df = pd.DataFrame(dataFram)\n",
    "# df['state'][4]\n",
    "# df.dtypes  #元素所有的类型\n",
    "# df.columns\n",
    "# type(df.index)\n",
    "# df.loc[0, 'state']\n",
    "# df.loc[1, ['state', 'year']]\n",
    "# df.loc[1, :]\n",
    "# df.loc[0:2, 'state':'pop']\n",
    "# df.loc[df['year'] >= 2002, :]\n",
    "df.iloc[1,2]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从dataFrame中查询出Series\n",
    "# 1.如果只查一列,结果是Series\n",
    "# df['state']\n",
    "# 2.如果查多列,结果是DataFrame\n",
    "# df[['state','year']]\n",
    "\n",
    "# 3.如果只查一行,结果是Series,index 是列名\n",
    "# df.loc[0]\n",
    "\n",
    "# 4.如果只查多行,结果是DataFrame,(dataFrame与python的区别,dataFram 包含末尾元素下表)\n",
    "df.loc[1:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 如何修改DataFrame中某一列的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['state'].replace({'a':'出账未确认','b':'正常','c':'逾期','d':'逾期在贷','e':'正常结清'},inplace=True)\n",
    "#A替换为aaa,B替换为bbb,4替换为100\n",
    "# df_1=df.replace(to_replace=['A','B',4],value=['aaa','bbb',100])\n",
    "\n",
    "#将A替换为AAAA\n",
    "# df_2=df.replace(to_replace='A',value='AAAA')\n",
    "\n",
    "#将A替换为AAAAA,5替换为2000\n",
    "# df_3=df.replace(to_replace={\"A\":'AAAAA',5:2000})\n",
    "\n",
    "# df.loc[:,\"气温(度)\"]=df[\"气温(度)\"].str.replace(\"℃\",\"\")\n",
    "# df[\"气温(度)\"].replace({\"℃\",\"\"},inplace=True)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "skullgrin",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.21"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
